USE [MSE6DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[UpdatePolicy]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[UpdatePolicy]
	@Name nvarchar(400),
	@Policy xml,
	@Description ntext = null,
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		UPDATE	ServiceEngine.Policies 
		SET		Policy = @Policy
		WHERE	PolicyID = @ID

		UPDATE	ServiceEngine.Names
		SET		EntityName = @Name,
				EntityDescription = @Description
		WHERE	EntityID = @ID and EntityClass = 8

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[CreatePolicy]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[CreatePolicy]
	@Name nvarchar(400),
	@Policy xml,
	@Description ntext = null,
	@ID uniqueidentifier OUTPUT
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SET NOCOUNT ON;

	IF @ID IS NULL 
		SET @ID = newid();

	BEGIN TRY
		BEGIN TRANSACTION

		INSERT INTO ServiceEngine.Policies (PolicyID, Policy)
		VALUES (@ID, @Policy)

		BEGIN TRY
			INSERT INTO ServiceEngine.Names (EntityID, EntityClass, EntityName, EntityDescription)
			VALUES (@ID, 8, @Name, @Description)
		END TRY
		BEGIN CATCH

			SELECT 
				@ErrorMessage = ''Attempt to insert Policy with duplicate name failed'',
				@ErrorSeverity = 11,
				@ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, 
					   @ErrorSeverity,
					   1);
		END CATCH

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[GetPolicy]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[GetPolicy]
	@ID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as PolicyID, N.[EntityName] as [Name], P.Policy, N.EntityDescription as Description
	FROM	[ServiceEngine].Policies P
	JOIN	[ServiceEngine].Names N on N.[EntityID] = P.PolicyID and N.[EntityClass] = 8
	WHERE	P.PolicyID = @ID

END




' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[DeletePolicy]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[DeletePolicy]
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DELETE	ServiceEngine.Policies 
		WHERE	PolicyID = @ID

		DELETE	ServiceEngine.Names
		WHERE	EntityID = @ID and EntityClass = 8

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END




' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[CreateMessenger]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[CreateMessenger]
	@Name nvarchar(400),
	@Configuration xml = null,
	@Description ntext = null,
	@ID uniqueidentifier OUTPUT
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	SET @ID = newid();

	BEGIN TRY
		BEGIN TRANSACTION

		INSERT INTO ServiceEngine.Messengers (MessengerID, Configuration)
		VALUES (@ID, @Configuration)

		BEGIN TRY
			INSERT INTO ServiceEngine.Names (EntityID, EntityClass, EntityName, EntityDescription)
			VALUES (@ID, 1, @Name, @Description)
		END TRY
		BEGIN CATCH

			SELECT 
				@ErrorMessage = ''Attempt to insert Messenger with duplicate name failed'',
				@ErrorSeverity = 11,
				@ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, 
					   @ErrorSeverity,
					   1);
		END CATCH

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END




' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[UpdateMessenger]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[UpdateMessenger]
	@Name nvarchar(400),
	@Configuration xml = null,
	@Description ntext = null,
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		UPDATE	ServiceEngine.Messengers 
		SET		Configuration = @Configuration
		WHERE	MessengerID = @ID

		UPDATE	ServiceEngine.Names
		SET		EntityName = @Name,
				EntityDescription = @Description
		WHERE	EntityID = @ID and EntityClass = 1

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END






' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[GetProperty]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[GetProperty]
	@ID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as PropertyID, N.[EntityName] as [Name], P.[Property], N.EntityDescription as Description
	FROM	[ServiceEngine].Properties P
	JOIN	[ServiceEngine].Names N on N.[EntityID] = P.PropertyID and N.[EntityClass] = 10
	WHERE	P.PropertyID = @ID

END




' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[UpdateProperty]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'




-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[UpdateProperty]
	@Name nvarchar(400),
	@Property xml,
	@Description ntext = null,
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		UPDATE	ServiceEngine.Properties 
		SET		[Property] = @Property
		WHERE	PropertyID = @ID

		UPDATE	ServiceEngine.Names
		SET		EntityName = @Name,
				EntityDescription = @Description
		WHERE	EntityID = @ID and EntityClass = 10

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END








' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[DeleteProperty]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[DeleteProperty]
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DELETE	ServiceEngine.Properties 
		WHERE	PropertyID = @ID

		DELETE	ServiceEngine.Names
		WHERE	EntityID = @ID and EntityClass = 10

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END




' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[CreateProperty]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[CreateProperty]
	@Name nvarchar(400),
	@Property xml,
	@Description ntext = null,
	@ID uniqueidentifier OUTPUT
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	SET @ID = newid();

	BEGIN TRY
		BEGIN TRANSACTION

		INSERT INTO ServiceEngine.Properties (PropertyID, [Property])
		VALUES (@ID, @Property)

		BEGIN TRY
			INSERT INTO ServiceEngine.Names (EntityID, EntityClass, EntityName, EntityDescription)
			VALUES (@ID, 10, @Name, @Description)
		END TRY
		BEGIN CATCH

			SELECT 
				@ErrorMessage = ''Attempt to insert Property with duplicate name failed'',
				@ErrorSeverity = 11,
				@ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, 
					   @ErrorSeverity,
					   1);
		END CATCH

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END




' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListPublishedOperationsForEndpoint]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:		William Oellermann
-- Create date: 12/12/06
-- Description:	
-- Modified date:  12/12/06
-- Description: Added isPublic Support
-- Modified date:  12/12/06
-- Description: Renamed to ListOperationsForEndpoint and changed relationship to operations from services
-- Modified date:  12/18/06
-- Description: Changed relationship to operation Versions from operations

-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListPublishedOperationsForEndpoint]
	@EndpointID uniqueidentifier

AS
BEGIN
	SET NOCOUNT ON;


	SELECT	OV.[OperationVersionID] as ID, N.[EntityName] as OpName, OV.[VersionMajor] as Major, OV.[VersionMinor] as Minor, OV.[VersionBuild] as Build, OV.[VersionQFE] as QFE
	FROM	[ServiceEngine].Endpoints_PublishedOperations PO 
	JOIN	[ServiceEngine].OperationVersions OV on PO.OperationVersionID = OV.OperationVersionID
	JOIN	[ServiceEngine].Names N on OV.OperationID = N.EntityID
	WHERE	PO.EndpointID = @EndpointID
	Order by N.[EntityName]

END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListSchemas]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListSchemas]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as SchemaID, N.[EntityName] as [Name], N.EntityDescription as Description
	FROM	[ServiceEngine].Names N
	WHERE	N.[EntityClass] = 9
	Order by N.[EntityName]
END





' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[UpdateSchema]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[UpdateSchema]
	@Name nvarchar(400),
	@Schema xml,
	@PreferredPrefix nvarchar(10),
	@Description ntext = null,
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		UPDATE	ServiceEngine.Schemas 
		SET		[Schema] = @Schema,
				PreferredPrefix = @PreferredPrefix
		WHERE	SchemaID = @ID

		UPDATE	ServiceEngine.Names
		SET		EntityName = @Name,
				EntityDescription = @Description
		WHERE	EntityID = @ID and EntityClass = 9

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END







' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[EntityExists]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[EntityExists]
	@EntityID uniqueidentifier,
	@EntityClass int,
	@EntityExists bit OUTPUT
AS
BEGIN
	SET NOCOUNT ON

	SELECT  @EntityExists = 1
	FROM	ServiceEngine.Names N
	WHERE	N.EntityClass	= @EntityClass
	AND		N.EntityID	= @EntityID
END

' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[CreateSchema]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[CreateSchema]
	@Name nvarchar(400),
	@Schema xml,
	@PreferredPrefix nvarchar(10),
	@Description ntext = null,
	@ID uniqueidentifier OUTPUT
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	SET @ID = newid();

	BEGIN TRY
		BEGIN TRANSACTION

		INSERT INTO ServiceEngine.Schemas (SchemaID, PreferredPrefix, [Schema])
		VALUES (@ID, @PreferredPrefix, @Schema)

		BEGIN TRY
			INSERT INTO ServiceEngine.Names (EntityID, EntityClass, EntityName, EntityDescription)
			VALUES (@ID, 9, @Name, @Description)
		END TRY
		BEGIN CATCH

			SELECT 
				@ErrorMessage = ''Attempt to insert Schema with duplicate namespace uri failed'',
				@ErrorSeverity = 11,
				@ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, 
					   @ErrorSeverity,
					   1);
		END CATCH

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END




' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[DeleteSchema]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[DeleteSchema]
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DELETE	ServiceEngine.Schemas 
		WHERE	SchemaID = @ID

		DELETE	ServiceEngine.Names
		WHERE	EntityID = @ID and EntityClass = 9

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END




' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListChannels]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'




-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListChannels]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as ChannelID, N.[EntityName] as [Name], N.EntityDescription as Description
	FROM	[ServiceEngine].Names N
	WHERE	N.[EntityClass] = 7
	Order by N.[EntityName]
END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[GetOperation]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[GetOperation]
	@ID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	
			N.EntityID as ID, 
			N.[EntityName] as [Name], 
			N.EntityDescription as Description, 
			O.PolicyID
	FROM	[ServiceEngine].Operations O
	JOIN	[ServiceEngine].Names N on N.[EntityID] = O.OperationID and N.[EntityClass] = 4
	WHERE	O.OperationID = @ID

END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListOperationsByElement]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListOperationsByElement]
	@SchemaID uniqueidentifier,
	@ElementName nvarchar(400) 
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	
			N.[EntityID] as ID, 
			N.[EntityName] as [Name], 
			N.EntityDescription as Description
	FROM	[ServiceEngine].Names N
	JOIN	[ServiceEngine].OperationVersions OV on N.[EntityID] = OV.OperationID
	JOIN	[ServiceEngine].Operations O on O.OperationID = OV.OperationID
	JOIN	[ServiceEngine].MessageParts MP on OV.OperationVersionID = MP.OperationVersionID
	WHERE	N.[EntityClass] = 4
	AND		MP.SchemaID = @SchemaID 
	AND		MP.ElementName = @ElementName
END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListOperationsBySchema]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListOperationsBySchema]
	@SchemaID uniqueidentifier 
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	
			N.[EntityID] as ID, 
			N.[EntityName] as [Name], 
			N.EntityDescription as Description
	FROM	[ServiceEngine].Names N
	JOIN	[ServiceEngine].OperationVersions OV on N.[EntityID] = OV.OperationID
	JOIN	[ServiceEngine].Operations O on O.OperationID = OV.OperationID
	JOIN	[ServiceEngine].MessageParts MP on OV.OperationVersionID = MP.OperationVersionID
	WHERE	N.[EntityClass] = 4
	AND		MP.SchemaID = @SchemaID 
END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListRuntimeServers]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListRuntimeServers]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as BrokerID, N.[EntityName] as [Name], N.EntityDescription as Description
	FROM	[ServiceEngine].Names N
	WHERE	N.[EntityClass] = 0
	Order by N.[EntityName]
END



' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListBindings]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListBindings]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as BindingID, N.[EntityName] as [Name], N.EntityDescription as Description
	FROM	[ServiceEngine].Names N
	WHERE	N.[EntityClass] = 6
	Order by N.[EntityName]
END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListSchemasForEndpoint]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- Modified Date:  11/22/06
-- Description: Onlhy listing the schemas of services that are public for the endpoint provided
-- Modified Date:  12/19/06
-- Description: Reflecting updated relationship between endpoint and operation version
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListSchemasForEndpoint]
	@EndpointID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as SchemaID, N.[EntityName] as [Name], S.[Schema], N.EntityDescription as Description
	FROM	[ServiceEngine].Names N
	JOIN	[ServiceEngine].MessageParts MP on MP.SchemaID = N.EntityID
	JOIN	[ServiceEngine].OperationVersions OV on OV.OperationVersionID = MP.OperationVersionID 	
	JOIN	[ServiceEngine].EndPoints_PublishedOperations EA on OV.OperationVersionID = EA.OperationVersionID
	JOIN	[ServiceEngine].Schemas S on N.[EntityID] = S.SchemaID
	WHERE	N.[EntityClass] = 9
	AND		EA.EndpointID = @EndpointID
	AND		OV.IsPublic = 1

	UNION ALL

	SELECT	N.[EntityID] as SchemaID, N.[EntityName] as [Name], S.[Schema], N.EntityDescription as Description
	FROM	[ServiceEngine].Names N
	JOIN	[ServiceEngine].SoapFaults SF on SF.SchemaID = N.EntityID
	JOIN	[ServiceEngine].OperationVersions OV on OV.OperationVersionID = SF.OperationVersionID 	
	JOIN	[ServiceEngine].EndPoints_PublishedOperations EA on OV.OperationVersionID = EA.OperationVersionID
	JOIN	[ServiceEngine].Schemas S on N.[EntityID] = S.SchemaID
	WHERE	N.[EntityClass] = 9
	AND		EA.EndpointID = @EndpointID
	AND		OV.IsPublic = 1

END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListOperations]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListOperations]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	
		N.[EntityID] as ID, 
		N.[EntityName] as [Name], 
		N.EntityDescription as Description
	FROM	[ServiceEngine].Names N 
	JOIN [ServiceEngine].Operations O ON O.OperationID = N.[EntityID] 
	WHERE	N.[EntityClass] = 4
	Order by N.[EntityName]
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[UpdateOperation]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[UpdateOperation]
	@Name nvarchar(400),
	@Description ntext = null,
	@PolicyID uniqueidentifier = null,
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY

		BEGIN TRANSACTION

		UPDATE	ServiceEngine.Operations
		SET		PolicyID = @PolicyID
		WHERE	OperationID = @ID

		UPDATE	ServiceEngine.Names
		SET		EntityName = @Name,
				EntityDescription = @Description
		WHERE	EntityID = @ID and EntityClass = 4

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[DeleteRuntimeServer]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[DeleteRuntimeServer]
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DELETE	ServiceEngine.RuntimeServers 
		WHERE	RuntimeServerID = @ID

		DELETE	ServiceEngine.Names
		WHERE	EntityID = @ID and EntityClass = 0

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListAssociatedOperationsForEndpoint]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'






-- =============================================
-- Author:		William Oellermann
-- Create date: 12/12/06
-- Description:	

-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListAssociatedOperationsForEndpoint]
	@EndpointID uniqueidentifier

AS
BEGIN
	SET NOCOUNT ON;

SELECT	OV.[OperationVersionID] as ID, N.[EntityName] as OpName, OV.[VersionMajor] as Major, OV.[VersionMinor] as Minor, OV.[VersionBuild] as Build, OV.[VersionQFE] as QFE
	FROM	[ServiceEngine].Endpoints_AssociatedOperations AO 
	JOIN	[ServiceEngine].OperationVersions OV on AO.OperationVersionID = OV.OperationVersionID
	JOIN	[ServiceEngine].Names N on OV.OperationID = N.EntityID
	WHERE	AO.EndpointID = @EndpointID
	Order By N.[EntityName]

END






' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[GetOperationVersionsForEndpointAndFirstBodyElement]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- Create date: 12/14/06
-- Description:	Updated for Associated Operation Versions
-- =============================================
CREATE PROCEDURE [ServiceEngine].[GetOperationVersionsForEndpointAndFirstBodyElement]
	@EndpointID uniqueidentifier,
	@ElementName nvarchar(4000),
	@namespaceURI nvarchar(4000)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SchemaID uniqueidentifier

	IF @ElementName is null  -- Blank Request Msg
	BEGIN
		SELECT	OV.OperationVersionID as ID, OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE,
				OV.AutoResponse, OV.IsPublic, OV.IsActive, OV.RequestTransformXSLT, OV.ResponseTransformXSLT, OV.ChannelID, OV.Moniker
				,OV.OperationID
		FROM	[ServiceEngine].OperationVersions OV
		JOIN	[ServiceEngine].EndPoints_AssociatedOperations AO on AO.OperationVersionID = OV.OperationVersionID
		WHERE	AO.EndpointID = @EndpointID
		AND		(OV.IsActive = 1 OR OV.IsTestable = 1)
		AND NOT EXISTS (
			SELECT 1
			FROM	[ServiceEngine].MessageParts MP
			WHERE	MP.OperationVersionID = OV.OperationVersionID
			AND		MP.IsRequestMsg = 1)
		ORDER BY OV.OperationID, OV.VersionMajor DESC, OV.VersionMinor DESC, OV.VersionBuild DESC, OV.VersionQFE DESC
	END
	ELSE
	BEGIN
		SELECT  @SchemaID = N.EntityID
		FROM	[ServiceEngine].Names N
		WHERE	N.EntityClass = 9
		AND		N.EntityName = @namespaceURI

		SELECT	OV.OperationVersionID as ID, OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE,
				OV.AutoResponse, OV.IsPublic, OV.IsActive, OV.RequestTransformXSLT, OV.ResponseTransformXSLT, OV.ChannelID, OV.Moniker
				,OV.OperationID
		FROM	[ServiceEngine].OperationVersions OV
		JOIN	[ServiceEngine].EndPoints_AssociatedOperations AO on AO.OperationVersionID = OV.OperationVersionID
		JOIN	[ServiceEngine].MessageParts MP on MP.OperationVersionID = OV.OperationVersionID
		WHERE	AO.EndpointID = @EndpointID
		AND		(OV.IsActive = 1 OR OV.IsTestable = 1)
		AND		MP.IsRequestMsg = 1
		AND		MP.Ordinal = 0
		AND		MP.SchemaID = @SchemaID
		AND		MP.ElementName = @ElementName
		ORDER BY OV.OperationID, OV.VersionMajor DESC, OV.VersionMinor DESC, OV.VersionBuild DESC, OV.VersionQFE DESC
	END

END



' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListAssociatedEndpointsForOperationVersionID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- Create date: 12/14/06
-- Description:	Updated to operation versions
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListAssociatedEndpointsForOperationVersionID]
	@OperationVersionID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as EndpointID, N.[EntityName] as URI
	FROM	[ServiceEngine].Endpoints_AssociatedOperations AO
	JOIN	[ServiceEngine].Names N on N.[EntityID] = AO.EndpointID and N.[EntityClass] = 2
	JOIN	[ServiceEngine].OperationVersions OV on OV.[OperationVersionID] = AO.[OperationVersionID]
	WHERE	AO.OperationVersionID = @OperationVersionID
	UNION
	SELECT	N.[EntityID] as EndpointID, N.[EntityName] as URI
	FROM	[ServiceEngine].Endpoints_PublishedOperations AO
	JOIN	[ServiceEngine].Names N on N.[EntityID] = AO.EndpointID and N.[EntityClass] = 2
	JOIN	[ServiceEngine].OperationVersions OV on OV.[OperationVersionID] = AO.[OperationVersionID]
	WHERE	AO.OperationVersionID = @OperationVersionID
	
END



' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListAssociatedEndpointsForOperationID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		William Oellermann
-- Create date: 12/14/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListAssociatedEndpointsForOperationID]
	@OperationID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as EndpointID, N.[EntityName] as URI
	FROM	[ServiceEngine].Endpoints_AssociatedOperations AO
	JOIN	[ServiceEngine].Names N on N.[EntityID] = AO.EndpointID and N.[EntityClass] = 2
	JOIN	[ServiceEngine].OperationVersions OV on OV.[OperationVersionID] = AO.[OperationVersionID]
	WHERE	OV.OperationID = @OperationID
	UNION
	SELECT	N.[EntityID] as EndpointID, N.[EntityName] as URI
	FROM	[ServiceEngine].Endpoints_PublishedOperations AO
	JOIN	[ServiceEngine].Names N on N.[EntityID] = AO.EndpointID and N.[EntityClass] = 2
	JOIN	[ServiceEngine].OperationVersions OV on OV.[OperationVersionID] = AO.[OperationVersionID]
	WHERE	OV.OperationID = @OperationID
END



' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[GetOperationVersionsForEndpointAndAction]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- Create date: 12/14/06
-- Description:	Updated to support Associated Operation Version on the Endpoint
-- =============================================
CREATE PROCEDURE [ServiceEngine].[GetOperationVersionsForEndpointAndAction]
	@EndpointID uniqueidentifier,
	@OperationName nvarchar(4000),
	@VersionMajor int,
	@VersionMinor int,
	@VersionBuild int,
	@VersionQFE int
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @OperationID uniqueidentifier

	IF @OperationName is null
	BEGIN
		SELECT	OV.OperationVersionID as ID, OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE,
				OV.AutoResponse, OV.IsTestable, OV.IsActive, OV.RequestTransformXSLT, OV.ResponseTransformXSLT, OV.ChannelID, OV.Moniker
				,OV.OperationID
		FROM	[ServiceEngine].OperationVersions OV		
		JOIN	[ServiceEngine].EndPoints_AssociatedOperations AO on AO.OperationVersionID = OV.OperationVersionID
		WHERE	AO.EndpointID = @EndpointID
		AND		(OV.IsActive = 1 OR OV.IsTestable = 1)
		ORDER BY OV.OperationID, OV.VersionMajor DESC, OV.VersionMinor DESC, OV.VersionBuild DESC, OV.VersionQFE DESC
	END
	ELSE
	BEGIN
		SELECT  @OperationID = N.EntityID
		FROM	[ServiceEngine].Names N
		WHERE	N.EntityClass = 4
		AND		N.EntityName = @OperationName

		IF @VersionMajor is null
		BEGIN
			SELECT	OV.OperationVersionID as ID, OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE,
					OV.AutoResponse, OV.IsTestable, OV.IsActive, OV.RequestTransformXSLT, OV.ResponseTransformXSLT, OV.ChannelID, OV.Moniker
					,OV.OperationID
			FROM	[ServiceEngine].OperationVersions OV
			JOIN	[ServiceEngine].EndPoints_AssociatedOperations AO on AO.OperationVersionID = OV.OperationVersionID
			WHERE	AO.EndpointID = @EndpointID
			AND		OV.OperationID = @OperationID
			AND		(OV.IsActive = 1 OR OV.IsTestable = 1)
			ORDER BY OV.OperationID, OV.VersionMajor DESC, OV.VersionMinor DESC, OV.VersionBuild DESC, OV.VersionQFE DESC
		END
		ELSE
		BEGIN
			IF @VersionMinor is null
			BEGIN
				SELECT	OV.OperationVersionID as ID, OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE,
						OV.AutoResponse, OV.IsTestable, OV.IsActive, OV.RequestTransformXSLT, OV.ResponseTransformXSLT, OV.ChannelID, OV.Moniker
						,OV.OperationID
				FROM	[ServiceEngine].OperationVersions OV
				JOIN	[ServiceEngine].EndPoints_AssociatedOperations AO on AO.OperationVersionID = OV.OperationVersionID
				WHERE	AO.EndpointID = @EndpointID
				AND		OV.OperationID = @OperationID
				AND		(OV.IsActive = 1 OR OV.IsTestable = 1)
				AND		OV.VersionMajor = @VersionMajor
				ORDER BY OV.OperationID, OV.VersionMajor DESC, OV.VersionMinor DESC, OV.VersionBuild DESC, OV.VersionQFE DESC
			END
			ELSE
			BEGIN
				IF @VersionBuild is null
				BEGIN
					SELECT	OV.OperationVersionID as ID, OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE,
							OV.AutoResponse, OV.IsTestable, OV.IsActive, OV.RequestTransformXSLT, OV.ResponseTransformXSLT, OV.ChannelID, OV.Moniker
							,OV.OperationID
					FROM	[ServiceEngine].OperationVersions OV
					JOIN	[ServiceEngine].EndPoints_AssociatedOperations AO on AO.OperationVersionID = OV.OperationVersionID
					WHERE	AO.EndpointID = @EndpointID
					AND		OV.OperationID = @OperationID
					AND		(OV.IsActive = 1 OR OV.IsTestable = 1)
					AND		OV.VersionMajor = @VersionMajor
					AND		OV.VersionMinor = @VersionMinor
					ORDER BY OV.OperationID, OV.VersionMajor DESC, OV.VersionMinor DESC, OV.VersionBuild DESC, OV.VersionQFE DESC
				END
				ELSE
				BEGIN
					IF @VersionQFE is null
					BEGIN
						SELECT	OV.OperationVersionID as ID, OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE,
								OV.AutoResponse, OV.IsTestable, OV.IsActive, OV.RequestTransformXSLT, OV.ResponseTransformXSLT, OV.ChannelID, OV.Moniker
								,OV.OperationID
						FROM	[ServiceEngine].OperationVersions OV
						JOIN	[ServiceEngine].EndPoints_AssociatedOperations AO on AO.OperationVersionID = OV.OperationVersionID
						WHERE	AO.EndpointID = @EndpointID
						AND		OV.OperationID = @OperationID
						AND		(OV.IsActive = 1 OR OV.IsTestable = 1)
						AND		OV.VersionMajor = @VersionMajor
						AND		OV.VersionMinor = @VersionMinor
						AND		OV.VersionBuild = @VersionBuild
						ORDER BY OV.OperationID, OV.VersionMajor DESC, OV.VersionMinor DESC, OV.VersionBuild DESC, OV.VersionQFE DESC
					END
					ELSE
					BEGIN
						SELECT	OV.OperationVersionID as ID, OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE,
								OV.AutoResponse, OV.IsTestable, OV.IsActive, OV.RequestTransformXSLT, OV.ResponseTransformXSLT, OV.ChannelID, OV.Moniker
								,OV.OperationID
						FROM	[ServiceEngine].OperationVersions OV
						JOIN	[ServiceEngine].EndPoints_AssociatedOperations AO on AO.OperationVersionID = OV.OperationVersionID
						WHERE	AO.EndpointID = @EndpointID
						AND		OV.OperationID = @OperationID
						AND		(OV.IsActive = 1 OR OV.IsTestable = 1)
						AND		OV.VersionMajor = @VersionMajor
						AND		OV.VersionMinor = @VersionMinor
						AND		OV.VersionBuild = @VersionBuild
						AND		OV.VersionQFE = @VersionQFE
						ORDER BY OV.OperationID, OV.VersionMajor DESC, OV.VersionMinor DESC, OV.VersionBuild DESC, OV.VersionQFE DESC
					END
				END
			END
		END

	END

END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[DeleteOperation]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[DeleteOperation]
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DELETE	ServiceEngine.Operations 
		WHERE	OperationID = @ID

		DELETE	ServiceEngine.Names
		WHERE	EntityID = @ID and EntityClass = 4

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[GetSchemabyNamespace]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



-- =============================================
-- Author:		William Oellermann
-- Create date: 1/16/07
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[GetSchemabyNamespace]
	@Namespace nvarchar(400)

AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as PolicyID, N.[EntityName] as [Name], S.PreferredPrefix, S.[Schema], N.EntityDescription as Description
	FROM	[ServiceEngine].Schemas S
	JOIN	[ServiceEngine].Names N on N.[EntityID] = S.SchemaID and N.[EntityClass] = 9
	WHERE	N.[EntityName] = @Namespace

END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[CreateBinding]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- Modified date:  1/8/06
-- Description: Changed from EndpointType to Binding
-- =============================================
CREATE PROCEDURE [ServiceEngine].[CreateBinding]
	@Name nvarchar(400),
	@BindingConfig xml,
	@Description ntext = null,
	@ID uniqueidentifier OUTPUT
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	IF @ID IS NULL 
		SET @ID = newid();

	BEGIN TRY
		BEGIN TRANSACTION

		INSERT INTO ServiceEngine.Bindings (BindingID, BindingConfig)
		VALUES (@ID, @BindingConfig)

		BEGIN TRY
			INSERT INTO ServiceEngine.Names (EntityID, EntityClass, EntityName, EntityDescription)
			VALUES (@ID, 6, @Name, @Description)
		END TRY
		BEGIN CATCH

			SELECT 
				@ErrorMessage = ''Attempt to insert Binding with duplicate name failed'',
				@ErrorSeverity = 11,
				@ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, 
					   @ErrorSeverity,
					   1);
		END CATCH

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END





' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[DeleteBinding]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[DeleteBinding]
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DELETE	ServiceEngine.Bindings 
		WHERE	BindingID = @ID

		DELETE	ServiceEngine.Names
		WHERE	EntityID = @ID and EntityClass = 6

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END




' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[UpdateBinding]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'




-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[UpdateBinding]
	@Name nvarchar(400),
	@BindingConfig xml,
	@Description ntext = null,
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		UPDATE	ServiceEngine.Bindings 
		SET		BindingConfig = @BindingConfig
		WHERE	BindingID = @ID

		UPDATE	ServiceEngine.Names
		SET		EntityName = @Name,
				EntityDescription = @Description
		WHERE	EntityID = @ID and EntityClass = 6

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[GetBinding]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'




-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[GetBinding]
	@ID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as BindingID, N.[EntityName] as [Name], B.BindingConfig, N.EntityDescription as Description
	FROM	[ServiceEngine].Bindings B
	JOIN	[ServiceEngine].Names N on N.[EntityID] = B.BindingID and N.[EntityClass] = 6
	WHERE	B.BindingID = @ID

END





' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[GetChannel]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'




-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[GetChannel]
	@ID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as EndpointID, N.[EntityName] as [Name], D.MonikerSchema, D.BindingID, D.EditorAssembly, D.EditorTypeName,  N.EntityDescription as Description
	FROM	[ServiceEngine].Channels D
	JOIN	[ServiceEngine].Names N on N.[EntityID] = D.ChannelID and N.[EntityClass] = 7
	WHERE	D.ChannelID = @ID

END






' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[UpdateChannel]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'




-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[UpdateChannel]
	@Name nvarchar(400),
	@MonikerSchema xml,
	@BindingID uniqueidentifier,
	@EditorAssembly nvarchar(max),
	@EditorTypeName nvarchar(max),
	@Description ntext = null,
	@ID uniqueidentifier 
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		UPDATE	ServiceEngine.Channels 
		SET		MonikerSchema = @MonikerSchema,
				BindingID = @BindingID,
				EditorAssembly = @EditorAssembly,
				EditorTypeName = @EditorTypeName
		WHERE	ChannelID = @ID

		UPDATE	ServiceEngine.Names
		SET		EntityName = @Name,
				EntityDescription = @Description 
		WHERE	EntityID = @ID and EntityClass = 7

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[DeleteChannel]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[DeleteChannel]
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DELETE	ServiceEngine.Channels 
		WHERE	ChannelID = @ID

		DELETE	ServiceEngine.Names
		WHERE	EntityID = @ID and EntityClass = 7

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END






' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[CreateChannel]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[CreateChannel]
	@Name nvarchar(400),
	@BindingID uniqueidentifier,
	@MonikerSchema xml,
	@EditorAssembly nvarchar(max),
	@EditorTypeName nvarchar(max),
	@Description ntext = null,
	@ID uniqueidentifier OUTPUT
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	IF @ID IS NULL 
		SET @ID = newid();

	BEGIN TRY
		BEGIN TRANSACTION

		INSERT INTO ServiceEngine.Channels (ChannelID, MonikerSchema, BindingID, EditorAssembly, EditorTypeName)
		VALUES (@ID, @MonikerSchema, @BindingID, @EditorAssembly, @EditorTypeName)

		BEGIN TRY
			INSERT INTO ServiceEngine.Names (EntityID, EntityClass, EntityName, EntityDescription)
			VALUES (@ID, 7, @Name, @Description)
		END TRY
		BEGIN CATCH

			SELECT 
				@ErrorMessage = ''Attempt to insert ChannelType with duplicate name failed'',
				@ErrorSeverity = 11,
				@ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, 
					   @ErrorSeverity,
					   1);
		END CATCH

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END







' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[DeleteOperationVersion]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'




-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[DeleteOperationVersion]
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DECLARE @OPID uniqueidentifier

		SELECT  @OPID = OV.OperationID
		FROM	[ServiceEngine].OperationVersions OV
		WHERE	OV.OperationVersionID = @ID

		SELECT  @@ROWCOUNT
		FROM	[ServiceEngine].OperationVersions OV
		WHERE	OV.OperationID = @OPID


		IF @@ROWCOUNT = 1 -- last Version so Delete master "operation"
		BEGIN
			DELETE	ServiceEngine.Operations 
			WHERE	OperationID = @OPID

			DELETE	ServiceEngine.Names
			WHERE	EntityID = @OPID and EntityClass = 4
		END

		DELETE	OperationVersions
		WHERE	OperationVersionID = @ID

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[CreateOperation]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'






-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[CreateOperation]
	@Name nvarchar(400),
	@PolicyID uniqueidentifier = null,
	@Description ntext = null,
	@ID uniqueidentifier OUTPUT
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		SET @ID = newid();

		BEGIN TRY
			INSERT INTO ServiceEngine.Names (EntityID, EntityClass, EntityName, EntityDescription)
			VALUES (@ID, 4, @Name, @Description)
		END TRY
		BEGIN CATCH

			SELECT 
				@ErrorMessage = ''Attempt to insert Operation with duplicate name failed'',
				@ErrorSeverity = 11,
				@ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, 
					   @ErrorSeverity,
					   1);
		END CATCH

		INSERT INTO ServiceEngine.Operations (OperationID, PolicyID)
		VALUES (@ID, @PolicyID)

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[GetOperationVersion]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[GetOperationVersion]
	@ID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	OV.OperationVersionID as ID, OV.OperationID as OperationID, N.[EntityName] as [Name], OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE,
			OV.AutoResponse, OV.IsPublic, OV.IsActive, OV.IsTestable, OV.RequestTransformXSLT, OV.ResponseTransformXSLT, OV.ChannelID, OV.Moniker, OV.Description			
	FROM	[ServiceEngine].OperationVersions OV
	JOIN	[ServiceEngine].Operations O ON OV.OperationID = O.OperationID
	JOIN	[ServiceEngine].[Names] N ON N.EntityID = O.OperationID
	WHERE	OV.OperationVersionID = @ID

END



' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[CreateOperationGroup]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[CreateOperationGroup]
	@Name nvarchar(400),
	@Description ntext = null,
	@ID uniqueidentifier OUTPUT
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	SET @ID = newid();

	BEGIN TRY
		BEGIN TRANSACTION

		INSERT INTO [ServiceEngine].[OperationGroups]([OperationGroupID])
		VALUES (@ID)

		BEGIN TRY
			INSERT INTO ServiceEngine.Names (EntityID, EntityClass, EntityName, EntityDescription)
			VALUES (@ID, 11, @Name, @Description)
		END TRY
		BEGIN CATCH

			SELECT 
				@ErrorMessage = ''Attempt to insert OperationGroup with duplicate name failed'',
				@ErrorSeverity = 11,
				@ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, 
					   @ErrorSeverity,
					   1);
		END CATCH

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END





' 
END
GO
/****** Object:  StoredProcedure [ServiceEngine].[CreateRuntimeServer]    Script Date: 05/19/2008 03:14:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		William Oellermann
-- Create date: 12/14/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[CreateRuntimeServer]
	@Name nvarchar(400),	
	@Description ntext = null,
	@CatalogURI ntext = null,
	@CatalogBindingID uniqueidentifier = null,
	@NotificationURI ntext = null,
	@NotificationBindingID uniqueidentifier = null,
	@BrokerURI ntext = null,
	@BrokerBindingID uniqueidentifier = null,
	@ListenerURI ntext = null,
	@ListenerBindingID uniqueidentifier = null,
	@HostName ntext = null,
	@ID uniqueidentifier OUTPUT
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	SET @ID = newid();

	BEGIN TRY
		BEGIN TRANSACTION

		INSERT INTO ServiceEngine.RuntimeServers (RuntimeServerID, CatalogURI,NotificationURI, BrokerURI, ListenerURI, CatalogBindingID, NotificationBindingID, BrokerBindingID, ListenerBindingID, HostName)
		VALUES (@ID, @CatalogURI, @NotificationURI, @BrokerURI, @ListenerURI, @CatalogBindingID,@NotificationBindingID, @BrokerBindingID, @ListenerBindingID, @HostName)

		BEGIN TRY
			INSERT INTO ServiceEngine.Names (EntityID, EntityClass, EntityName, EntityDescription)
			VALUES (@ID, 0, @Name, @Description)
		END TRY
		BEGIN CATCH

			SELECT 
				@ErrorMessage = 'Attempt to insert RuntimeServer with duplicate name failed',
				@ErrorSeverity = 11,
				@ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, 
					   @ErrorSeverity,
					   1);
		END CATCH

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END
GO

/****** Object:  StoredProcedure [ServiceEngine].[GetRuntimeServer]    Script Date: 05/19/2008 03:14:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		William Oellermann
-- Create date: 12/6/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[GetRuntimeServer]
	@ID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as RuntimeServerID, 
			N.[EntityName] as Name, 
			A.CatalogURI, 
			A.CatalogBindingID, 
			A.NotificationURI, 
			A.NotificationBindingID, 
			A.BrokerURI, 
			A.BrokerBindingID, 
			A.ListenerURI, 
			A.ListenerBindingID,
			A.HostName, 
			N.EntityDescription as Description
	FROM	[ServiceEngine].RuntimeServers A
	JOIN	[ServiceEngine].Names N on N.[EntityID] = A.RuntimeServerID and N.[EntityClass] = 0
	WHERE	A.RuntimeServerID = @ID

END
GO


/****** Object:  StoredProcedure [ServiceEngine].[UpdateRuntimeServer]    Script Date: 05/19/2008 03:14:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- Modified date:12/14/06
-- Description:	Changed to RuntimeServer
-- =============================================
CREATE PROCEDURE [ServiceEngine].[UpdateRuntimeServer]
	@Name nvarchar(400),	
	@Description ntext = null,
	@CatalogURI ntext = null,
	@CatalogBindingID uniqueidentifier = null,
	@NotificationURI ntext = null,
	@NotificationBindingID uniqueidentifier = null,
	@BrokerURI ntext = null,
	@BrokerBindingID uniqueidentifier = null,
	@ListenerURI ntext = null,
	@ListenerBindingID uniqueidentifier = null,
	@HostName ntext = null,
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		UPDATE	ServiceEngine.RuntimeServers 
		SET		NotificationURI = @NotificationURI,
				CatalogURI = @CatalogURI,
				BrokerURI = @BrokerURI,
				ListenerURI = @ListenerURI,
				NotificationBindingID = @NotificationBindingID,
				CatalogBindingID = @CatalogBindingID,
				BrokerBindingID = @BrokerBindingID,
				ListenerBindingID = @ListenerBindingID,
				HostName = @HostName
		WHERE	RuntimeServerID = @ID

		UPDATE	ServiceEngine.Names
		SET		EntityName = @Name,
				EntityDescription = @Description
		WHERE	EntityID = @ID and EntityClass = 0

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[UpdateOperationGroup]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'




-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- Create date: 12/14/06
-- Description:	Updated to Operation Group
-- =============================================
CREATE PROCEDURE [ServiceEngine].[UpdateOperationGroup]
	@Name nvarchar(400),
	@Description ntext = null,
	@ID uniqueidentifier,
	@PolicyID uniqueidentifier = null
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		UPDATE	ServiceEngine.Operations 
		SET		PolicyID = @PolicyID
		WHERE	OperationID = @ID

		UPDATE	ServiceEngine.Names
		SET		EntityName = @Name,
				EntityDescription = @Description
		WHERE	EntityID = @ID and EntityClass = 11

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[GetOperationGroup]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'




-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[GetOperationGroup]
	@ID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as OperationGroupID, N.[EntityName] as [Name], N.EntityDescription as Description
	FROM	[ServiceEngine].OperationGroups O
	JOIN	[ServiceEngine].Names N on N.[EntityID] = O.OperationGroupID and N.[EntityClass] = 11
	WHERE	O.OperationGroupID = @ID

END





' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListPolicies]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListPolicies]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as PolicyID, N.[EntityName] as [Name], N.EntityDescription as Description
	FROM	[ServiceEngine].Names N
	WHERE	N.[EntityClass] = 8
	Order by N.[EntityName]
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[DeleteOperationGroup]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[DeleteOperationGroup]
	@ID uniqueidentifier,
	@Name nvarchar(400) OUTPUT
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	SELECT	
			@Name = N.[EntityName]
	FROM	[ServiceEngine].Names N 
	WHERE	N.[EntityID]  = @ID and N.[EntityClass] = 11
	
	BEGIN TRY
		BEGIN TRANSACTION

		DELETE	ServiceEngine.OperationGroups 
		WHERE	OperationGroupID = @ID

		DELETE	ServiceEngine.Names
		WHERE	EntityID = @ID and EntityClass = 11

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END

' 
END
GO

/****** Object:  StoredProcedure [ServiceEngine].[ListEndpointsForRuntimeServerID]    Script Date: 05/19/2008 03:14:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:
-- Modified date: 12/14/06
-- Description:	Changed from Messenger to RuntimeServer	
-- Modified date: 5/14/08
-- Description:	Separating hostname from the endpoint	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListEndpointsForRuntimeServerID]
	@RuntimeServerID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

SELECT	N.[EntityID] as EndpointID, ISNULL(RS.[HostName],'') +':'+ CAST(EP.[Port] AS NVARCHAR(16))+'/' + N.[EntityName] as URI, EP.[Port], N.EntityDescription as Description
	FROM	[ServiceEngine].RuntimeServers_Endpoints RE
	JOIN	[ServiceEngine].Names N on N.[EntityID] = RE.EndpointID and N.[EntityClass] = 2
	JOIN	[ServiceEngine].EndPoints EP on RE.EndpointID = EP.EndpointID
	JOIN [ServiceEngine].RuntimeServers RS on RE.RuntimeServerID = RS.RuntimeServerID	
	WHERE	RE.RuntimeServerID = @RuntimeServerID
	Order by N.[EntityName]
END
GO
/****** Object:  StoredProcedure [ServiceEngine].[CreateEndpoint]    Script Date: 05/19/2008 03:20:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[CreateEndpoint]
	@Name nvarchar(400),
	@BindingID uniqueidentifier,
	@PolicyID uniqueidentifier,
	@Description ntext = null,
	@NamespaceURI ntext = null,
	@TestCallsAccepted bit = 0,
	@AutoOperationPubUpdate bit = 0,
	@AutoOperationActivationUpdate bit = 0,
	@Configuration xml = null,
	@ServiceName nvarchar(400) = null,
	@Port smallint = null,
	@ID uniqueidentifier OUTPUT
	
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	SET @ID = newid();

	BEGIN TRY
		BEGIN TRANSACTION

		INSERT INTO ServiceEngine.Endpoints (EndpointID, BindingID, PolicyID, NamespaceURI, TestCallsAccepted, AutoOperationPubUpdate, AutoOperationActivationUpdate, Configuration, ServiceName, Port)
		VALUES (@ID, @BindingID, @PolicyID, @NamespaceURI, @TestCallsAccepted, @AutoOperationPubUpdate, @AutoOperationActivationUpdate, @Configuration, @ServiceName, @Port)

		BEGIN TRY
			INSERT INTO ServiceEngine.Names (EntityID, EntityClass, EntityName, EntityDescription)
			VALUES (@ID, 2, @Name, @Description)
		END TRY
		BEGIN CATCH

			SELECT 
				@ErrorMessage = 'Attempt to insert Endpoint with duplicate URI failed',
				@ErrorSeverity = 11,
				@ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, 
					   @ErrorSeverity,
					   1);
		END CATCH

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH
END


/****** Object:  StoredProcedure [ServiceEngine].[GetEndpoint]    Script Date: 05/19/2008 03:14:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[GetEndpoint]
	@ID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as EndpointID, N.[EntityName] as Path, E.BindingID, E.PolicyID, N.EntityDescription as Description, 
		E.NamespaceURI, E.TestCallsAccepted, E.AutoOperationPubUpdate, E.AutoOperationActivationUpdate, E.Configuration, E.ServiceName, E.Port
	FROM	[ServiceEngine].Endpoints E
	JOIN	[ServiceEngine].Names N on N.[EntityID] = E.EndpointID and N.[EntityClass] = 2
	WHERE	E.EndpointID = @ID

END
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[GetMessage]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[GetMessage]
	@OperationVersionID uniqueidentifier,
	@IsRequestMsg bit
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	MP.MessagePartID, MP.OperationVersionID, MP.ElementName, N.EntityName as NamespaceURI
	FROM	[ServiceEngine].MessageParts MP
	JOIN	[ServiceEngine].Schemas S on S.SchemaID = MP.SchemaID
	JOIN	[ServiceEngine].Names N on N.[EntityID] = S.SchemaID and N.[EntityClass] = 9
	WHERE	MP.OperationVersionID = @OperationVersionID
	AND		MP.IsRequestMsg = @IsRequestMsg
	ORDER BY MP.Ordinal

END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListProperties]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListProperties]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as PropertyID, N.[EntityName] as [Name], N.EntityDescription as Description
	FROM	[ServiceEngine].Names N
	WHERE	N.[EntityClass] = 10
END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[GetSchema]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[GetSchema]
	@ID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as PolicyID, N.[EntityName] as [Name], S.PreferredPrefix, S.[Schema], N.EntityDescription as Description
	FROM	[ServiceEngine].Schemas S
	JOIN	[ServiceEngine].Names N on N.[EntityID] = S.SchemaID and N.[EntityClass] = 9
	WHERE	S.SchemaID = @ID

END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[GetIDFromName]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[GetIDFromName]
	@EntityName nvarchar(400),
	@EntityClass int,
	@EntityID uniqueidentifier OUTPUT
AS
BEGIN
	SET NOCOUNT ON

	SELECT  @EntityID = N.EntityID
	FROM	ServiceEngine.Names N
	WHERE	N.EntityClass	= @EntityClass
	AND		N.EntityName	= @EntityName
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListEndpoints]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListEndpoints]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as EndpointID, N.[EntityName] as URI, N.EntityDescription as Description
	FROM	[ServiceEngine].Names N
	WHERE	N.[EntityClass] = 2
	Order by N.[EntityName]
END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[DeleteEndpoint]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[DeleteEndpoint]
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DELETE	ServiceEngine.Endpoints 
		WHERE	EndpointID = @ID

		DELETE	ServiceEngine.Names
		WHERE	EntityID = @ID and EntityClass = 2

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListOperationGroups]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'




-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListOperationGroups]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	N.[EntityID] as OperationGroupID, N.[EntityName] as [Name], N.EntityDescription as Description
	FROM	[ServiceEngine].Names N
	WHERE	N.[EntityClass] = 11
	Order by N.[EntityName]
END


' 
END
GO
/****** Object:  StoredProcedure [ServiceEngine].[UpdateEndpoint]    Script Date: 05/19/2008 03:14:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[UpdateEndpoint]
	@Name nvarchar(400),
	@BindingID uniqueidentifier,
	@PolicyID uniqueidentifier,
	@NamespaceURI ntext = null,
	@TestCallsAccepted bit = null,
	@AutoOperationPubUpdate bit = null,
	@AutoOperationActivationUpdate bit = null,
	@Description ntext = null,
	@Configuration xml = null,
	@ServiceName nvarchar(400) = null,
	@Port int = 0,
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		UPDATE	ServiceEngine.Endpoints 
		SET		BindingID = @BindingID,
				NamespaceURI = @NamespaceURI,
				TestCallsAccepted = @TestCallsAccepted,
				AutoOperationPubUpdate = @AutoOperationPubUpdate,
				AutoOperationActivationUpdate = @AutoOperationActivationUpdate,
				Configuration = @Configuration,
				ServiceName = @ServiceName,	
				Port = @Port,	
				PolicyID = @PolicyID
		WHERE	EndpointID = @ID

		UPDATE	ServiceEngine.Names
		SET		EntityName = @Name,
				EntityDescription = @Description
		WHERE	EntityID = @ID and EntityClass = 2

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[SubscribeToEvent]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [ServiceEngine].[SubscribeToEvent] 
	@EventName varchar(100),
	@CallbackURI varchar(200),
	@Expires datetime OUTPUT 
AS
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	Declare @EventID as uniqueidentifier
	
	BEGIN TRY
		BEGIN TRANSACTION

	Select  @EventID = EventID
	From	[ServiceEngine].[Events] e
	Where	e.[Name] = @EventName
	
	if (@EventID is not null) BEGIN
	
		set		@Expires = dateadd(hour,8,getdate())
		
		update [ServiceEngine].[EventRegistrations]
		set		Expires = @Expires
		where	EventID = @EventID
		and		CallbackURI = @CallbackURI
		
		if (@@ROWCOUNT = 0) BEGIN
		
			Insert Into [ServiceEngine].[EventRegistrations] (EventID, CallbackURI, Expires)
			Values (@EventID, @CallbackURI, @Expires)
		
		END

		COMMIT TRANSACTION	
	END 

	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH
	
	RETURN




' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[UnsubscribeFromEvent]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [ServiceEngine].[UnsubscribeFromEvent] 
	@EventName varchar(100),
	@CallbackURI varchar(200)
AS
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	Declare @EventID as uniqueidentifier

	BEGIN TRY
		BEGIN TRANSACTION
	
		Select  @EventID = EventID
		From	[ServiceEngine].[Events] e
		Where	e.[Name] = @EventName
		
		if (@EventID is not null) BEGIN

			Delete	[ServiceEngine].[EventRegistrations]
			where	EventID = @EventID
			and		CallbackURI = @CallbackURI
			
		END 

		delete	[ServiceEngine].[EventRegistrations]
		where	Expires <= GetDate()	

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		BEGIN TRANSACTION

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH
		
RETURN

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListCallbackURIsByEventName]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [ServiceEngine].[ListCallbackURIsByEventName]
@EventName as varchar(100)

AS

Begin

select  ER.CallbackURI
from	[ServiceEngine].EventRegistrations ER
join	[ServiceEngine].Events E on E.EventID = ER.EventID
where	E.[Name] = @EventName
and		ER.Expires > GetDate()

End

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListOperationVersions]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListOperationVersions]
	@OperationID as uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	OV.OperationVersionID as ID, OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE, OV.Description
	FROM	OperationVersions OV
	WHERE	OV.OperationID = @OperationID
END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[CreateOperationVersion]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[CreateOperationVersion]
	@OperationID uniqueidentifier,
	@VersionMajor int,
	@VersionMinor int,
	@VersionBuild int,
	@VersionQFE int,
	@Description ntext = null,
	@AutoResponse Xml = null,
	@IsPublic bit = 0,
	@IsActive bit = 0,
	@IsTestable bit = 0,
	@RequestTransform xml = null,
	@ResponseTransform xml = null,
	@ChannelID uniqueidentifier = null,
	@Moniker xml = null,	
	@ID uniqueidentifier OUTPUT
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		SET @ID = newid()

		INSERT INTO ServiceEngine.OperationVersions (OperationVersionID, OperationID, VersionMajor, VersionMinor, VersionBuild, VersionQFE,
					Description, AutoResponse, IsPublic, IsActive, IsTestable, RequestTransformXSLT, ResponseTransformXSLT, ChannelID, Moniker)
		VALUES (@ID, @OperationID, @VersionMajor, @VersionMinor, @VersionBuild, @VersionQFE,
					@Description, @AutoResponse, @IsPublic, @IsActive, @IsTestable, @RequestTransform, @ResponseTransform, @ChannelID, @Moniker)

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END



' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[DeleteVersionsFromOperation]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'




-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[DeleteVersionsFromOperation]
	@OperationID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DELETE	OperationVersions
		WHERE	OperationID = @OperationID

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[UpdateOperationVersion]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[UpdateOperationVersion]
	@VersionMajor int,
	@VersionMinor int,
	@VersionBuild int,
	@VersionQFE int,
	@Description ntext = null,
	@AutoResponse Xml,
	@IsPublic bit,
	@IsActive bit,
	@IsTestable bit,
	@RequestTransform xml = null,
	@ResponseTransform xml = null,
	@ChannelID uniqueidentifier = null,
	@Moniker xml = null,
	@ID uniqueidentifier 
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		UPDATE	ServiceEngine.OperationVersions 
		SET		VersionMajor = @VersionMajor,
				VersionMinor = @VersionMinor, 
				VersionBuild = @VersionBuild, 
				VersionQFE = @VersionQFE,
				Description = @Description,
				AutoResponse = @AutoResponse, 
				IsPublic = @IsPublic, 
				IsActive = @IsActive,
				IsTestable = @IsTestable,
				RequestTransformXSLT = @RequestTransform,
				ResponseTransformXSLT = @ResponseTransform,
				ChannelID = @ChannelID,
				Moniker = @Moniker
		WHERE	OperationVersionID = @ID

		--Delete any Public associations with EndPoint
		  IF @IsPublic = 0 
		  BEGIN
			DELETE ServiceEngine.Endpoints_PublishedOperations 
			WHERE OperationVersionID = @ID
		  END

		  --Delete any Active associations with EndPoint
		  IF @IsActive = 0 AND @IsTestable = 0
		  BEGIN
			  DELETE ServiceEngine.Endpoints_AssociatedOperations 
			  WHERE OperationVersionID = @ID
		  END

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END





' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[AssignPublishedOperationVersionToEndpoint]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- Update date:  11/22/06 - WO
-- Description:  refactor to operationversion from service
-- =============================================
CREATE PROCEDURE [ServiceEngine].[AssignPublishedOperationVersionToEndpoint]
	@OperationVersionID uniqueidentifier,
	@EndpointID uniqueidentifier

AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DECLARE @OperationID uniqueidentifier

		SET @OperationID = (SELECT OperationID FROM ServiceEngine.OperationVersions 
		WHERE OperationVersionID = @OperationVersionID)

		DECLARE @ID as uniqueidentifier

		SET @ID = newid();

		INSERT	ServiceEngine.EndPoints_PublishedOperations (EndPoints_PublishedOperationsID, OperationVersionID, EndpointID, OperationID)
		VALUES	(@ID, @OperationVersionID, @EndpointID, @OperationID)

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListOperationVersionsByElement]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListOperationVersionsByElement]
	@SchemaID uniqueidentifier,
	@ElementName nvarchar(400) 
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	DISTINCT OV.OperationVersionID as ID
	FROM	[ServiceEngine].OperationVersions OV
	JOIN	[ServiceEngine].MessageParts MP on OV.OperationVersionID = MP.OperationVersionID
	WHERE	MP.SchemaID = @SchemaID 
	AND		MP.ElementName = @ElementName
END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListOperationVersionsBySchema]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListOperationVersionsBySchema]
	@SchemaID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	DISTINCT OV.OperationVersionID as ID
	FROM	[ServiceEngine].OperationVersions OV
	JOIN	[ServiceEngine].MessageParts MP on OV.OperationVersionID = MP.OperationVersionID
	WHERE	MP.SchemaID = @SchemaID 
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[RemovePublishedOperationFromEndpoint]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- Mofified date: 12/14/06
-- Description:	Associated Operations
-- =============================================
CREATE PROCEDURE [ServiceEngine].[RemovePublishedOperationFromEndpoint]
	@OperationVersionID uniqueidentifier,
	@EndpointID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DELETE	ServiceEngine.Endpoints_PublishedOperations 
		WHERE	OperationVersionID = @OperationVersionID
		AND		EndpointID = @EndpointID

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[RemoveAssociatedOperationFromEndpoint]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- Mofified date: 12/14/06
-- Description:	Associated Operations
-- =============================================
CREATE PROCEDURE [ServiceEngine].[RemoveAssociatedOperationFromEndpoint]
	@OperationVersionID uniqueidentifier,
	@EndpointID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DELETE	ServiceEngine.Endpoints_AssociatedOperations 
		WHERE	OperationVersionID = @OperationVersionID
		AND		EndpointID = @EndpointID

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END



' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[AssignAssociatedOperationVersionToEndpoint]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- Update date:  11/22/06 - WO
-- Description:  refactor to operationversion from service
-- =============================================
CREATE PROCEDURE [ServiceEngine].[AssignAssociatedOperationVersionToEndpoint]
	@OperationVersionID uniqueidentifier,
	@EndpointID uniqueidentifier

AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DECLARE @ID as uniqueidentifier

		SET @ID = newid();

		INSERT	ServiceEngine.EndPoints_AssociatedOperations (EndPoints_AssociatedOperationsID, OperationVersionID, EndpointID)
		VALUES	(@ID, @OperationVersionID, @EndpointID)

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END


' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[CreateEndpointUddiAssociation]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- Update date:  11/22/06 - WO
-- Description:  refactor to operationversion from service
-- =============================================
CREATE PROCEDURE [ServiceEngine].[CreateEndpointUddiAssociation]
	@EndpointID uniqueidentifier,
	@RuntimeServerID uniqueidentifier,
	@ServiceKey nvarchar(64),
	@TModelKey nvarchar(64),
	@BindingKey nvarchar(64),
	@WsdlUrl nvarchar(400),
	@BindingUrl nvarchar(400)
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DECLARE @ID as uniqueidentifier

		SET @ID = newid();

		INSERT INTO [ServiceEngine].[EndPoints_AssociatedUddi]
				   ([EndPoints_AssociatedUddiID]
				   ,[RuntimeServerID]
				   ,[EndpointID]
				   ,[UddiTModelID]
				   ,[UddiServiceID]
				   ,[UddiBindingID]
				   ,[UddiWsdlUrl]
				   ,[UddiBindingUrl]
				   )
			 VALUES
				   (@ID,@RuntimeServerID,@EndpointID,@TModelKey,@ServiceKey,@BindingKey,@WsdlUrl,@BindingUrl)

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END


' 
END
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[UpdateEndpointUddiAssociation]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- Update date:  11/22/06 - WO
-- Description:  refactor to operationversion from service
-- =============================================
CREATE PROCEDURE [ServiceEngine].[UpdateEndpointUddiAssociation]	
	@EndpointID uniqueidentifier,
	@RuntimeServerID uniqueidentifier,
	@WsdlUrl nvarchar(400),
	@BindingUrl nvarchar(400)
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DECLARE @ID as uniqueidentifier

		SET @ID = newid();

		UPDATE [MSE6DB].[ServiceEngine].[EndPoints_AssociatedUddi]
	    SET 
		  [UddiWsdlUrl] = @WsdlUrl
		  ,[UddiBindingUrl] = @BindingUrl
	    WHERE
			EndPointID = @EndpointID AND 
			RuntimeServerID = @RuntimeServerID
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END


' 
END
GO




SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[AssignOperationToOperationGroup]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- Update Author:  William Oellermann
-- Modified date: 12/14/06
-- Description:	Changed to AssignEndpointToRuntimeServer
-- =============================================
CREATE PROCEDURE [ServiceEngine].[AssignOperationToOperationGroup]
	@OperationGroupID uniqueidentifier,
	@OperationID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DECLARE @ID as uniqueidentifier
		
		SELECT	Operations_OperationGroupsID
		FROM	ServiceEngine.Operations_OperationGroups
		WHERE	OperationGroupID = @OperationGroupID
		AND		OperationID = @OperationID
		
		IF (@@ROWCOUNT = 0) 
		BEGIN
			SET @ID = newid();

			INSERT	ServiceEngine.Operations_OperationGroups (Operations_OperationGroupsID, OperationGroupID, OperationID)
			VALUES	(@ID, @OperationGroupID, @OperationID)
		END

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[AssignEndpointToRuntimeServer]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- Update Author:  William Oellermann
-- Modified date: 12/14/06
-- Description:	Changed to AssignEndpointToRuntimeServer
-- =============================================
CREATE PROCEDURE [ServiceEngine].[AssignEndpointToRuntimeServer]
	@RuntimeServerID uniqueidentifier,
	@EndpointID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DECLARE @ID as uniqueidentifier
		
		SELECT	RowID
		FROM	ServiceEngine.RuntimeServers_Endpoints
		WHERE	RuntimeServerID = @RuntimeServerID
		AND		EndpointID = @EndpointID
		
		IF (@@ROWCOUNT = 0) 
		BEGIN
			SET @ID = newid();

			INSERT	ServiceEngine.RuntimeServers_Endpoints (RowID, RuntimeServerID, EndpointID)
			VALUES	(@ID, @RuntimeServerID, @EndpointID)
		END

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[RemoveEndpointsFromRuntimeServer]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[RemoveEndpointsFromRuntimeServer]
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DELETE	ServiceEngine.RuntimeServers_Endpoints 
		WHERE	RuntimeServerID = @ID

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[DeleteMessagesFromOperationVersion]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'




-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[DeleteMessagesFromOperationVersion]
	@ID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DELETE	ServiceEngine.MessageParts 
		WHERE	OperationVersionID = @ID

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   @ErrorState);
	END CATCH

END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[CreateMessagePart]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[CreateMessagePart]
	@OperationVersionID uniqueidentifier,
	@IsRequestMsg bit,
	@Ordinal int,
	@ElementName nvarchar(400),
	@SchemaID uniqueidentifier,
	@ID uniqueidentifier OUTPUT
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	SET @ID = newid();

	BEGIN TRY
		BEGIN TRANSACTION

		INSERT INTO ServiceEngine.MessageParts (MessagePartID, OperationVersionID, IsRequestMsg, Ordinal, ElementName, SchemaID)
		VALUES (@ID, @OperationVersionID, @IsRequestMsg, @Ordinal, @ElementName, @SchemaID)

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END

' 
END
GO
USE [MSE6DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListOperationsByGroup]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'




-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListOperationsByGroup]
		@operationGroupID uniqueidentifier	
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	
		N.[EntityID] as ID, 
		N.[EntityName] as [Name], 
		N.EntityDescription as Description
	FROM	[ServiceEngine].Names N 
	JOIN [ServiceEngine].Operations O ON O.OperationID = N.[EntityID] 
	JOIN [ServiceEngine].Operations_OperationGroups OO ON OO.OperationID = O.OperationID
	JOIN [ServiceEngine].OperationGroups OG ON OO.OperationGroupID = OG.OperationGroupID
	WHERE	N.[EntityClass] = 4 AND 
			OG.OperationGroupID = @operationGroupID
	Order by N.[EntityName]
END


' 
END
GO
USE [MSE6DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[RemoveOperationFromOperationGroup]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Naveed Zaheer
-- Create date: 03/05/07
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[RemoveOperationFromOperationGroup]
	@OperationGroupID uniqueidentifier,
	@OperationID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DELETE	ServiceEngine.Operations_OperationGroups 
		WHERE 
				OperationGroupID = @OperationGroupID AND 
				OperationID = @OperationID

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END


' 
END
GO



USE [MSE6DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[RemoveEndpointUddiAssociation]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Naveed Zaheer
-- Create date: 03/05/07
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[RemoveEndpointUddiAssociation]
	@EndpointID uniqueidentifier,
	@RuntimeServerID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DELETE FROM [ServiceEngine].[EndPoints_AssociatedUddi]
		WHERE EndpointID = @EndpointID AND RuntimeServerID = @RuntimeServerID

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END
' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListEndpointUddiAssociation]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'




-- =============================================
-- Author:		Wolf Gilbert
-- Create date: 7/16/06
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListEndpointUddiAssociation]
	@ID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT [EndPoints_AssociatedUddiID]
      ,[EndpointID]
      ,[UddiTModelID]
      ,[UddiServiceID]
      ,[UddiBindingID]
      ,[UddiWsdlUrl]
      ,[UddiBindingUrl]
	  ,[RuntimeServerID]
FROM 
		[ServiceEngine].[EndPoints_AssociatedUddi]
	WHERE
		EndpointID = @ID
END
' 
END
GO


USE [MSE6DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListOperationGroupsByOperation]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:		Naveed Zaheer
-- Create date: 03/13/07
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[ListOperationGroupsByOperation]
		@operationID uniqueidentifier	
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	
		N.[EntityID] as ID, 
		N.[EntityName] as [Name], 
		N.EntityDescription as Description
	FROM	[ServiceEngine].Names N 
	JOIN [ServiceEngine].OperationGroups OG ON OG.OperationGroupID = N.[EntityID] 
	JOIN [ServiceEngine].Operations_OperationGroups OO ON OO.OperationGroupID = OG.OperationGroupID
	WHERE	N.[EntityClass] = 11 AND 
			OO.OperationID = @operationID
END
' 
END

GO
USE [MSE6DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[GetEntityNameFromID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:  Brian Otto
-- Create date: 12/11/07
-- Description: 
-- =============================================
CREATE PROCEDURE [ServiceEngine].GetEntityNameFromID
 @EntityID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON
	SELECT n.EntityName, n.EntityClass
	FROM [ServiceEngine].Names n
	WHERE n.EntityID = @EntityID
END
'
END


GO
USE [MSE6DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].ListRuntimeServersForOperationID') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:  Brian Otto
-- Create date: 12/11/07
-- Description: 
-- =============================================
CREATE PROCEDURE [ServiceEngine].ListRuntimeServersForOperationID
 @OperationID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON
	SELECT DISTINCT rse.RuntimeServerID, rse.EndpointID, ov.OperationID
	FROM ServiceEngine.OperationVersions ov
		INNER JOIN ServiceEngine.Endpoints_AssociatedOperations eao ON eao.OperationVersionID = ov.OperationVersionID
		INNER JOIN ServiceEngine.RuntimeServers_Endpoints rse ON eao.EndpointID = rse.EndpointID
	WHERE ov.OperationID = @OperationID
	UNION
	SELECT DISTINCT rse.RuntimeServerID, rse.EndpointID, ov.OperationID
	FROM ServiceEngine.OperationVersions ov
		INNER JOIN ServiceEngine.Endpoints_PublishedOperations epo ON epo.OperationVersionID = ov.OperationVersionID
		INNER JOIN ServiceEngine.RuntimeServers_Endpoints rse ON epo.EndpointID = rse.EndpointID
	WHERE ov.OperationID = @OperationID
END
'
END


GO
USE [MSE6DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListRuntimeServersForOperationVersionID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:  Brian Otto
-- Create date: 12/11/07
-- Description: 
-- =============================================
CREATE PROCEDURE [ServiceEngine].ListRuntimeServersForOperationVersionID
 @OperationVersionID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON
	SELECT DISTINCT rse.RuntimeServerID, rse.EndpointID, ov.OperationID
	FROM ServiceEngine.OperationVersions ov
		INNER JOIN ServiceEngine.Endpoints_AssociatedOperations eao ON eao.OperationVersionID = ov.OperationVersionID
		INNER JOIN ServiceEngine.RuntimeServers_Endpoints rse ON eao.EndpointID = rse.EndpointID
	WHERE eao.OperationVersionID = @OperationVersionID
	UNION
	SELECT DISTINCT rse.RuntimeServerID, rse.EndpointID, ov.OperationID
	FROM ServiceEngine.OperationVersions ov
		INNER JOIN ServiceEngine.Endpoints_PublishedOperations epo ON epo.OperationVersionID = ov.OperationVersionID
		INNER JOIN ServiceEngine.RuntimeServers_Endpoints rse ON epo.EndpointID = rse.EndpointID
	WHERE epo.OperationVersionID = @OperationVersionID
END
'
END


GO
USE [MSE6DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[ListRuntimeServersForEndpointID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:  Brian Otto
-- Create date: 12/11/07
-- Description: 
-- =============================================
CREATE PROCEDURE [ServiceEngine].ListRuntimeServersForEndpointID
 @EndpointID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON
	SELECT DISTINCT rse.RuntimeServerID, rse.EndpointID
	FROM ServiceEngine.RuntimeServers_Endpoints rse
	WHERE rse.EndpointID = @EndpointID
END
'
END

GO
USE [MSE6DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[RemoveEndpointFromRuntimeServer]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:  Brian Otto
-- Create date: 4/2/08
-- Description: 
-- =============================================
CREATE PROCEDURE [ServiceEngine].[RemoveEndpointFromRuntimeServer]
 @RuntimeServerID uniqueidentifier,
 @EndpointID uniqueidentifier

AS
BEGIN
 DECLARE @ErrorMessage NVARCHAR(4000);
 DECLARE @ErrorSeverity INT;
 DECLARE @ErrorState INT;
 SET NOCOUNT ON;

 BEGIN TRY
  BEGIN TRANSACTION

  DELETE ServiceEngine.RuntimeServers_Endpoints 
  WHERE RuntimeServerID = @RuntimeServerID AND EndpointID = @EndpointID

  COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
  SELECT 
   @ErrorMessage = ERROR_MESSAGE(),
   @ErrorSeverity = ERROR_SEVERITY(),
   @ErrorState = ERROR_STATE();

  IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

  RAISERROR (@ErrorMessage, 
       @ErrorSeverity,
       1);
 END CATCH
 END
 '
 END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[GetSoapFaultsForOperationVersion]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:		Naveed Zaheer
-- Create date: 05/05/08
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[GetSoapFaultsForOperationVersion]
	@OperationVersionID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SchemaID uniqueidentifier

	SELECT 
		N.EntityName as NamespaceUri,
		SF.[SoapFaultID],
		SF.[Name],
		SF.[Action],
		SF.[ProtectionLevel],
		SF.[ElementName]
	FROM
		ServiceEngine.SoapFaults SF 
	JOIN 
		ServiceEngine.Names N ON SF.SchemaID = N.EntityID
	WHERE
		SF.OperationVersionID = @OperationVersionID

END



' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[CreateSoapFaultForOperationVersion]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Naveed Zaheer
-- Create date: 05/05/08
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[CreateSoapFaultForOperationVersion]
	@OperationVersionID uniqueidentifier,
	@Name nvarchar(400),
	@ElementName nvarchar(400),
	@Action nvarchar(256),
	@ProtectionLevel nvarchar(32),
	@SchemaID uniqueidentifier,
	@ID uniqueidentifier OUTPUT
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	SET @ID = newid();

	BEGIN TRY
		BEGIN TRANSACTION

		INSERT INTO ServiceEngine.SoapFaults (OperationVersionID,SoapFaultID, Name, ElementName, Action, ProtectionLevel, SchemaID)
		VALUES (@OperationVersionID,@ID, @Name, @ElementName, @Action,@ProtectionLevel, @SchemaID)

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[DeleteSoapFaultsForOperationVersion]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Naveed Zaheer
-- Create date: 05/05/08
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[DeleteSoapFaultsForOperationVersion]
	@OperationVersionID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DELETE 
				[MSE6DB].[ServiceEngine].[SoapFaults]
		WHERE 
				[OperationVersionID] = @OperationVersionID
		 
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END

' 
END
GO
USE [MSE6DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[AssignEntityPolicy]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:  Sash George
-- Create date: 5/6/08
-- Description: 
-- =============================================
CREATE PROCEDURE [ServiceEngine].[AssignEntityPolicy]
	@EntityID uniqueidentifier,
	@PolicyID uniqueidentifier

AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DECLARE @ID as uniqueidentifier

		SET @ID = newid();

		INSERT	ServiceEngine.Entity_Policies (EntityID, PolicyID)
		VALUES	(@EntityID, @PolicyID)

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH

END
'
END

GO

USE [MSE6DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[DeletePoliciesFromEntity]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Sash George
-- Create date: 5/6/08
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[DeletePoliciesFromEntity]
	@EntityID uniqueidentifier
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		DELETE	ServiceEngine.Entity_Policies 
		WHERE	EntityID = @EntityID

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   @ErrorState);
	END CATCH

END'
END

GO

USE [MSE6DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ServiceEngine].[GetEntityPolicies]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		Sash George
-- Create date: 5/6/08
-- Description:	
-- =============================================
CREATE PROCEDURE [ServiceEngine].[GetEntityPolicies]
	@EntityID uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	EP.PolicyID, EP.EntityID, N.EntityName as [Name], N.EntityDescription as Description
	FROM	[ServiceEngine].Entity_Policies EP, [ServiceEngine].Names N
	WHERE	EP.EntityID = @EntityID and
	N.[EntityID] = EP.PolicyID and N.[EntityClass] = 8
END'
END
GO
